const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

// Open database
const db = new Database('./data/delivery-management.db');

// Create backup directory if it doesn't exist
const backupDir = './sql/backup';
if (!fs.existsSync(backupDir)) {
    fs.mkdirSync(backupDir, { recursive: true });
}

console.log('创建数据库备份...');

// Get all tables
const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'").all();

// Create full backup SQL
let fullBackup = '';

// Add schema first
const schema = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'").all();
fullBackup += schema.map(r => r.sql).join(';\n') + ';\n\n';

// Add data for each table
tables.forEach(table => {
    const tableName = table.name;
    console.log(`备份表: ${tableName}`);
    
    try {
        const rows = db.prepare(`SELECT * FROM ${tableName}`).all();
        
        if (rows.length > 0) {
            // Get column names
            const columns = Object.keys(rows[0]);
            
            rows.forEach(row => {
                const values = columns.map(col => {
                    const value = row[col];
                    if (value === null) return 'NULL';
                    if (typeof value === 'string') return `'${value.replace(/'/g, "''")}'`;
                    return value;
                });
                
                fullBackup += `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${values.join(', ')});\n`;
            });
        }
    } catch (error) {
        console.error(`Error backing up table ${tableName}:`, error.message);
    }
});

// Write full backup
fs.writeFileSync(path.join(backupDir, 'full-backup.sql'), fullBackup);

console.log('备份完成！');
console.log(`备份文件: ${path.join(backupDir, 'full-backup.sql')}`);

// Get file size
const stats = fs.statSync(path.join(backupDir, 'full-backup.sql'));
console.log(`文件大小: ${(stats.size / 1024 / 1024).toFixed(2)} MB`);

db.close();